<?php

//date_default_timezone_set('Europe/London');



/** PHPExcel_IOFactory

  $basedir=$_SERVER["DOCUMENT_ROOT"];

  include  $basedir.'/PHPExcel/Classes/PHPExcel/IOFactory.php';

 */
include '../Classes/PHPExcel/IOFactory.php';

//WD
//include '../../php/dbconfig.php';

class ImportExcel {
    /* function ImportExcel($localhostMysql,$userNameMysql,$passWordMysql,$dbNameMysql)

      {

      global $localhost,$userName,$passWord,$dbName;

      $localhost=$localhostMysql;

      $userName=$userNameMysql;

      $passWord=$passWordMysql;

      $dbName=$dbNameMysql;

      } */
    
    	//在php中去除utf8串空格返回
		function utf8trim($str)
		{
		   $nstr=preg_replace('/[(\xc2\xa0)]+/', ' ', $str); 
		   return trim($nstr);
		}
		
        function DropSp($sp_name) {
        $sql = "drop procedure if exists " . $sp_name;


        if (mysql_query($sql) == false) {
            return mysql_error();
        }



        return "Drop Sp Finished"; //正确
    }

    function BuildRandTableName($tableName) {
        $tableName = $tableName . mt_rand(1, 10000);
        return $tableName;
    }
    
    

    function ImportExcel() {

        $this->OpenConnection();

        //mysql_query("set character set 'utf8'");//读库
        //mysql_query("set names 'utf8'");//写库
    }

    function StartImportExcel($excelFile, $importType="tmp_event", $importSheetIndex=0) {

        //$importType表示当前上传的文件为tmp_event(event)/tmp_event_type/tmp_venue的文件
        //$importSheetIndex=0,当前文件被读取的sheetindex
        //1.检查文件是否存在

        if (!file_exists($excelFile)) {

            exit("The File (" . $excelFile . ") not found!</br>");
        }

        //2.装载文件

        $objPHPExcel = PHPExcel_IOFactory::load($excelFile);



        //3.打开数据库
        //$this->OpenConnection($localhostMysql,$userNameMysql,$passWordMysql,$dbNameMysql);
        //$this->OpenConnection();
        //4.处理第一个Sheet:Type
        //$result=$this->ImportSheetType($objPHPExcel,$importSheetIndex);//$importSheetIndex=0
        //5.处理第二个Sheet:master

        switch ($importType) {

            case "tmp_event":

//                        switch($importSheetIndex)
//
//                        {
//
//                            case "0":
//
//                                $result=$this->ImportSheetType($objPHPExcel,$importSheetIndex);//$importSheetIndex=0
//
//                                break;
//
//                            case "1":
//
//                                $result=$this->ImportSheetEvent($objPHPExcel,$importSheetIndex);//$importSheetIndex=1
//
//                                break;
//
//                            default:
//
//                                break;
//
//                        }
                $result = $this->ImportSheetEvent($objPHPExcel, $importSheetIndex); //$importSheetIndex=0
                break;

            case "tmp_event_type":

                $result = $this->ImportSheetEventType($objPHPExcel, $importSheetIndex);

                break;

            case "tmp_venue":

                $result = $this->ImportSheetVenue($objPHPExcel, $importSheetIndex);

                break;

            case "tmp_city":

                $result = $this->ImportSheetCity($objPHPExcel, $importSheetIndex);

                break;

            default:

                $result = "importType is wrang!";

                break;
        }



        //$string = "The quick' brown \"fox\" jumped \\ over the lazy dog.";
        //echo $string."</br>";
        //$result=$this->replaceSqlStr($string);

        return $result;
    }

    //打开数据库连接$localhost,$userName,$passWord,$dbName

    function OpenConnection() {

        /*

          global $localhost,$userName,$passWord,$dbName;

          //change to your database server/user name/password

          mysql_connect($localhost,$userName,$passWord) or

          die("Could not connect: " . mysql_error());

          //change to your database name

          mysql_select_db($dbName) or

          die("Could not select database: " . mysql_error());

         */

        $db = new DBConnection();

        $db->getConnection();
    }

    //处理写入mysql中的串包含"  '    \ 这样的特殊符号

    function ReplaceSqlStr($string) {



        $patterns[0] = "/\"/i";

        $patterns[1] = "/\'/i";

        //$patterns[2] = "/\\/i";



        $replacements[0] = "\\\\\"";

        $replacements[1] = "\\\\'";

        //$replacements[2] = "\\\\";



        ksort($patterns);

        ksort($replacements);



        return preg_replace($patterns, $replacements, $string);
    }

    //导入示例文件中的第一个sheet:Type,返回"Finished"代表导入成功，否则返回错误描述

    function ImportSheetType($objPHPExcel, $excelSheetIndex) {

        $currentSheet = $objPHPExcel->getSheet($excelSheetIndex);

        $allRow = $currentSheet->getHighestRow();



        $dtName="tmp_types";
        $spName = "prcd_".$this->BuildRandTableName($dtName). "_importExcel2007";
        $sql1 = "drop procedure if exists " . $spName . "";



        //debug

        if (mysql_query($sql1) == false) {
            return mysql_error();
        }



        $sql2 = "

        create procedure " . $spName . "()

        begin delete from " . $dtName . ";";



        for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {

            //$sql2.= "

            $sql2.= "

            insert into " . $dtName . " 

                (A,B,C,D,E,F,G,H)

             values 

                 ('" . $this->utf8trim($currentSheet->getCell('A' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($this->replaceSqlStr($currentSheet->getCell('B' . $currentRow)->getValue()))
                    . "','" . $this->utf8trim($currentSheet->getCell('C' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('D' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('E' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('F' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('G' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('H' . $currentRow)->getValue()) . "');";



            // echo $sql2;
        }

        $sql2.=" DELETE FROM " . $dtName . "

WHERE TRIM(A)='' AND TRIM(B)='' AND TRIM(C)='' AND TRIM(E)='' AND TRIM(E)='' AND TRIM(F)='' AND TRIM(G)='' AND TRIM(H)='';";

        $sql2 .= " end";



        if (mysql_query($sql2) == false) {
            return mysql_error();
        }



        $sql3 = "call " . $spName . "()";



        if (mysql_query($sql3) == false) {
            $this->DropSp($spName);
            return mysql_error();
        }
        $this->DropSp($spName);


        return "Finished"; //正确
    }

    //导入示例文件中的第二个sheet:master,返回"Finished"代表导入成功，否则返回错误描述

    function ImportSheetEvent($objPHPExcel, $excelSheetIndex) {

        $currentSheet = $objPHPExcel->getSheet($excelSheetIndex);

        $allRow = $currentSheet->getHighestRow();



        $dtName="tmp_event";
        $spName = "prcd_".$this->BuildRandTableName($dtName). "_importExcel2007";

        $sql1 = "drop procedure if exists " . $spName ;



        //debug

        if (mysql_query($sql1) == false) {
            return mysql_error();
        }



        $sql2 = "

        create procedure " . $spName . "()

        begin delete from " . $dtName . ";";



        //--插入数据

        for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {

            //$sql2.= "    //(festival,programName,event_type,country_region,site,startDate,endDate,palnTime)

            $fValue = $currentSheet->getCell('F' . $currentRow)->getValue();

            $gValue = $currentSheet->getCell('G' . $currentRow)->getValue();

            $hValue = $currentSheet->getCell('H' . $currentRow)->getValue();



            $start_date_unixtime = PHPExcel_Shared_Date::ExcelToPHP($fValue);

            $end_date_unixtime = PHPExcel_Shared_Date::ExcelToPHP($gValue);

            $time_unixtime = PHPExcel_Shared_Date::ExcelToPHP($hValue);

            $zone_second = 8 * 3600; //时区调整,应该可配置，或表中带入,或设置PHP的时区后就不要此项，或为0

            $start_date = date('Y-m-d', $start_date_unixtime - $zone_second);

            $end_date = date('Y-m-d', $end_date_unixtime - $zone_second);

            $time = date('H:i:s', $time_unixtime - $zone_second);



            if (!is_numeric($fValue)) {

                $start_date = "";
            }

            if (!is_numeric($gValue)) {

                $end_date = "";
            }

            if (!is_numeric($hValue)) {

                $time = "";
            }



            $sql2.= "

            insert into " . $dtName . " 

                (A,B,C,D,E,F,G,H)

             values 

                 ('" . $this->utf8trim($currentSheet->getCell('A' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($this->ReplaceSqlStr($currentSheet->getCell('B' . $currentRow)->getValue()))
                    . "','" . $this->utf8trim($currentSheet->getCell('C' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('D' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('E' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($start_date)
                    . "','" . $this->utf8trim($end_date)
                    . "','" . $this->utf8trim($time)
                    . "');";



            /* 这样的写法要注意时区

              ."','".date('r',PHPExcel_Shared_Date::ExcelToPHP($currentSheet->getCell('F'.$currentRow)->getValue()))

              ."','".date('r',PHPExcel_Shared_Date::ExcelToPHP($currentSheet->getCell('G'.$currentRow)->getValue()))

              ."','".date('r',PHPExcel_Shared_Date::ExcelToPHP($currentSheet->getCell('H'.$currentRow)->getValue()))

             * */

            // echo $sql2;
        }





        //清理空白行

        $sql2 .= " DELETE FROM " . $dtName . "

			WHERE TRIM(A)='' AND TRIM(B)='' AND TRIM(C)='' AND TRIM(D)='' AND TRIM(E)='' AND TRIM(F)='' AND TRIM(G)='' AND TRIM(H)='';";



        //校验event_type(种类）

        $sql2 .= "UPDATE " . $dtName . ",event_type	

		SET " . $dtName . ".existEtid=event_type.etid

		WHERE " . $dtName . ".C=event_type.name_zh_hk ;";



        //校验venue（场地）

        $sql2.="UPDATE " . $dtName . ",city	,venue

		SET " . $dtName . ".existVid=venue.vid

		WHERE " . $dtName . ".D=city.name_zh_hk AND venue.cid=city.cid AND " . $dtName . ".E=venue.name_zh_hk;";



        //校验city（地区）

        $sql2.="UPDATE " . $dtName . ",city	

		SET " . $dtName . ".existCid=city.Cid

		WHERE " . $dtName . ".D=city.name_zh_hk ;";





        //标记是否存在相同的event

        $sql2.="UPDATE " . $dtName . ",event	

		SET " . $dtName . ".existEid=event.eid

		WHERE " . $dtName . ".B=event.subject AND "
                . $dtName . ".existEtid=event.etid AND "
                . $dtName . ".existVid=event.vid AND event.delete_flag=0

		AND CASE WHEN " . $dtName . ".F='' THEN NULL ELSE " . $dtName . ".F END =CAST(event.start_time AS DATE) AND 

		CASE WHEN " . $dtName . ".G='' THEN NULL ELSE " . $dtName . ".G END =CAST(event.end_time AS DATE) ;";



        //标识start_date>end_date的异常

        $sql2.="UPDATE tmp_event SET checkResult=1 WHERE F>G AND G!='';";



        //标识end_date和time同时为null的异常

        $sql2.="UPDATE tmp_event SET checkResult=1 WHERE G='' AND H='';";



        //标识所有存在异常的记录

        $sql2.="UPDATE " . $dtName . "

		SET checkResult=1

		WHERE existEtid IS NULL OR existVid IS NULL OR existCid IS NULL OR existEid IS NOT NULL ;";





        $sql2 .= " end";




        if (mysql_query($sql2) == false) {
           return mysql_error();
        }



        $sql3 = "call " . $spName . "()";



        if (mysql_query($sql3) == false) {
            $this->DropSp($spName);
            $err = mysql_error();

            return $err;
        }

        $this->DropSp($spName);

        return "Finished"; //正确
    }

    //导入event_type,返回"Finished"代表导入成功，否则返回错误描述

    function ImportSheetEventType($objPHPExcel, $excelSheetIndex) {

        $currentSheet = $objPHPExcel->getSheet($excelSheetIndex);

        $allRow = $currentSheet->getHighestRow();



        $dtName="tmp_event_type";
        $spName = "prcd_".$this->BuildRandTableName($dtName). "_importExcel2007";

        $sql1 = "drop procedure if exists " . $spName ;



        //debug

        if (mysql_query($sql1) == false) {
            return mysql_error();
        }



        $sql2 = "

        create procedure " . $spName . "()

        begin delete from " . $dtName . ";";



        for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {

            //$sql2.= "

            $A = $this->utf8trim($currentSheet->getCell('A' . $currentRow)->getValue());

            $B = $this->utf8trim($currentSheet->getCell('B' . $currentRow)->getValue());

            $C = $this->utf8trim($currentSheet->getCell('C' . $currentRow)->getValue());

            $sql2.= "

            insert into " . $dtName . " 

                (A,B,C)

             values 

                 ('" . $A
                    . "','" . $B
                    . "','" . $C . "');";



            // echo $sql2;
        }

        //清理空白行

        $sql2.=" DELETE FROM " . $dtName . "

WHERE TRIM(A)='' AND TRIM(B)='' AND TRIM(C)='';";



        //校验event_type(种类）

        $sql2 .= "UPDATE " . $dtName . ",event_type	

		SET " . $dtName . ".existEtid=event_type.etid

		WHERE " . $dtName . ".B=event_type.name_zh_hk and event_type.delete_flag=0;";



        //标识所有存在异常的记录

        $sql2.="UPDATE " . $dtName . "

		SET checkResult=1

		WHERE " . $dtName . ".B IS NULL OR TRIM(" . $dtName . ".B)='' ;";



        //标识所有存在异常的记录

        $sql2.="UPDATE " . $dtName . "

		SET checkResult=1

		WHERE existEtid IS NOT NULL ;";



        $sql2 .= " end";



        if (mysql_query($sql2) == false) {
            return mysql_error();
        }



        $sql3 = "call " . $spName . "()";


        if (mysql_query($sql3) == false) {
            $this->DropSp($spName);
            return mysql_error();
        }


        $this->DropSp($spName);
        return "Finished"; //正确
    }

    function ImportSheetCity($objPHPExcel, $excelSheetIndex) {

        $currentSheet = $objPHPExcel->getSheet($excelSheetIndex);

        $allRow = $currentSheet->getHighestRow();



        $dtName="tmp_city";
        $spName = "prcd_".$this->BuildRandTableName($dtName). "_importExcel2007";

        $sql1 = "drop procedure if exists " . $spName ;



        //debug

        if (mysql_query($sql1) == false) {
            return mysql_error();
        }



        $sql2 = "

        create procedure " . $spName . "()

        begin delete from " . $dtName . ";";



        for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {

            //$sql2.= "

            $A = $this->utf8trim($currentSheet->getCell('A' . $currentRow)->getValue());

            $B = $this->utf8trim($currentSheet->getCell('B' . $currentRow)->getValue());

            $C = $this->utf8trim($currentSheet->getCell('C' . $currentRow)->getValue());

            $sql2.= "

            insert into " . $dtName . " 

                (A,B,C)

             values 

                 ('" . $A
                    . "','" . $B
                    . "','" . $C . "');";



            // echo $sql2;
        }

        //清理空白行

        $sql2.=" DELETE FROM " . $dtName . "

                WHERE TRIM(A)='' AND TRIM(B)='' AND TRIM(C)='';";



        //校验city

        $sql2 .= "UPDATE " . $dtName . ",city

		SET " . $dtName . ".existCid=city.cid

		WHERE " . $dtName . ".B=city.name_zh_hk and city.delete_flag=0;";



        //标识所有存在异常的记录

        $sql2.="UPDATE " . $dtName . "

		SET checkResult=1

		WHERE " . $dtName . ".B IS NULL OR TRIM(" . $dtName . ".B)='' ;";



        //标识所有存在异常的记录

        $sql2.="UPDATE " . $dtName . "

		SET checkResult=1

		WHERE existCid IS NOT NULL ;";



        $sql2 .= " end";



        if (mysql_query($sql2) == false) {
            return mysql_error();
        }



        $sql3 = "call " . $spName . "()";



        if (mysql_query($sql3) == false) {
            $this->DropSp($spName);
            return mysql_error();
        }

        $this->DropSp($spName);

        return "Finished"; //正确
    }

    //导入venue,返回"Finished"代表导入成功，否则返回错误描述

    function ImportSheetVenue($objPHPExcel, $excelSheetIndex) {

        $currentSheet = $objPHPExcel->getSheet($excelSheetIndex);

        $allRow = $currentSheet->getHighestRow();



        $dtName="tmp_venue";
        $spName = "prcd_".$this->BuildRandTableName($dtName). "_importExcel2007";

        $sql1 = "drop procedure if exists " . $spName ;



        //debug

        if (mysql_query($sql1) == false) {
            return mysql_error();
        }



        $sql2 = "

        create procedure " . $spName . "()

        begin delete from " . $dtName . ";";



        //--插入数据

        for ($currentRow = 2; $currentRow <= $allRow; $currentRow++) {



            $sql2.= "

            insert into " . $dtName . " 

                (A,B,C,D,E,F,G,H,I,J)

             values 

                 ('" . $this->utf8trim($currentSheet->getCell('A' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('B' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('C' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('D' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('E' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('F' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('G' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('H' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('I' . $currentRow)->getValue())
                    . "','" . $this->utf8trim($currentSheet->getCell('J' . $currentRow)->getValue())
                    . "');";
        }





        //清理空白行

        $sql2 .= " DELETE FROM " . $dtName . "

			WHERE TRIM(A)='' AND TRIM(B)='' AND TRIM(C)='' AND TRIM(D)='' AND TRIM(E)='' AND TRIM(F)='' AND TRIM(G)='' AND TRIM(H)='' AND TRIM(I)='' AND TRIM(J)='';";



        //校验city（地区）

        $sql2.="UPDATE " . $dtName . ",city	

		SET " . $dtName . ".existCid=city.Cid

		WHERE " . $dtName . ".H=city.name_zh_hk ;";





        //标记是否存在相同的event

        $sql2.="UPDATE `" . $dtName . "`,venue	

		SET " . $dtName . ".existVid=venue.vid

		WHERE " . $dtName . ".B=venue.name_zh_hk AND venue.delete_flag=0 ;";



        //标识所有存在异常的记录

        $sql2.="UPDATE " . $dtName . "

		SET checkResult=1

		WHERE " . $dtName . ".B IS NULL OR TRIM(" . $dtName . ".B)='' ;";



        //标识所有存在异常的记录

        $sql2.="UPDATE " . $dtName . "

		SET checkResult=1

		WHERE existCid IS NULL OR existVid IS NOT NULL ;";





        $sql2 .= " end";



        if (mysql_query($sql2) == false) {
            return mysql_error();
        }



        $sql3 = "call " . $spName . "()";



        if (mysql_query($sql3) == false) {
            $this->DropSp($spName);
            return mysql_error();
        }

        $this->DropSp($spName);

        return "Finished"; //正确
    }



}

?>